package br.com.siscov.model.funcionario.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import br.com.siscov.exceptions.IntegrationException;
import br.com.siscov.model.conexao.DriverManager;
import br.com.siscov.model.vo.AtendenteVo;
import br.com.siscov.model.vo.LoginVo;

public class AtendenteDAO extends DriverManager implements AtendenteInterfaceDao {

	public void incluirAtendente(Connection connection, Integer matricula, String login) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(false);

			String query = "insert into atendente (Funcionario_matricula,Acesso_login)values(?,?);";
			statement = connection.prepareStatement(query);

			statement.setInt(1, matricula);
			statement.setString(2, login);

			statement.executeUpdate();


		} catch (SQLException sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}

	}

	public void incluirFuncionarioAtendente(Connection connection, AtendenteVo atendenteVo) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(Boolean.FALSE);

			String query = "insert into funcionario(matricula,nome,dataNasc,telefone,sexo,rg,orgEmissor,cpf,escolaridade,cursos,cartTrabalho,logradouro,numero,cep,bairro,subBairro,dataAdm,valorPassagem,salario,cargo)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			statement = connection.prepareStatement(query);
			int i = 1;
			statement.setInt(i, atendenteVo.getMatricula());
			statement.setString(++i, atendenteVo.getNome());
			statement.setDate(++i, new Date(atendenteVo.getDatanasc().getTime()));
			statement.setString(++i, atendenteVo.getTelefone());
			statement.setString(++i, atendenteVo.getSexo());
			statement.setLong(++i, atendenteVo.getRg());
			statement.setString(++i, atendenteVo.getOrgEmissor());
			statement.setString(++i, atendenteVo.getCpf());
			statement.setString(++i, atendenteVo.getEscolaridade());
			statement.setString(++i, atendenteVo.getCursos());
			statement.setInt(++i, atendenteVo.getNumCartTrabalho());
			statement.setString(++i, atendenteVo.getLogradouro());
			statement.setInt(++i, atendenteVo.getNumero());
			statement.setString(++i, atendenteVo.getCep());
			statement.setString(++i, atendenteVo.getBairro());
			statement.setString(++i, atendenteVo.getSubBairro());
			statement.setDate(++i, new Date(atendenteVo.getDataAdmissao().getTime()));
			statement.setDouble(++i, atendenteVo.getVlpassagem().doubleValue());
			statement.setDouble(++i, atendenteVo.getSalario().doubleValue());
			statement.setString(++i, atendenteVo.getCargo());

			statement.executeUpdate();

		} catch (SQLException sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}

	}

	public AtendenteVo obterAtendente(Integer matricula) throws IntegrationException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		AtendenteVo atendenteVo = null;

		try {

			connection = obterConexao();
			String query = "SELECT funcionario.matricula, funcionario.nome, funcionario.dataNasc, funcionario.telefone, funcionario.sexo, funcionario.rg, funcionario.orgEmissor, funcionario.cpf, funcionario.escolaridade, funcionario.cursos, funcionario.cartTrabalho, funcionario.logradouro, funcionario.numero, funcionario.cep, funcionario.bairro, funcionario.subBairro, funcionario.dataAdm, funcionario.dataDem, funcionario. valorPassagem, funcionario.salario, funcionario. cargo, atendente.idAtendente, atendente.Acesso_login FROM siscov.atendente INNER JOIN siscov.funcionario ON (funcionario.matricula="+matricula+")";
			statement = connection.prepareStatement(query);

			//statement.setInt(1, matricula);

			resultSet = statement.executeQuery();

			atendenteVo = new AtendenteVo();
			LoginVo loginVo = new LoginVo();

			if (resultSet.next()) {

				atendenteVo.setMatricula(resultSet.getInt("matricula"));
				atendenteVo.setNome(resultSet.getString("nome"));
				atendenteVo.setDatanasc(resultSet.getDate("dataNasc"));
				atendenteVo.setTelefone(resultSet.getString("telefone"));
				atendenteVo.setSexo(resultSet.getString("sexo"));
				atendenteVo.setRg(resultSet.getLong("rg"));
				atendenteVo.setOrgEmissor(resultSet.getString("orgEmissor"));
				atendenteVo.setCpf(resultSet.getString("cpf"));
				atendenteVo.setEscolaridade(resultSet.getString("escolaridade"));
				atendenteVo.setCursos(resultSet.getString("cursos"));
				atendenteVo.setNumCartTrabalho(resultSet.getInt("cartTrabalho"));
				atendenteVo.setLogradouro(resultSet.getString("logradouro"));
				atendenteVo.setNumero(resultSet.getInt("numero"));
				atendenteVo.setCep(resultSet.getString("cep"));
				atendenteVo.setBairro(resultSet.getString("bairro"));
				atendenteVo.setSubBairro(resultSet.getString("subBairro"));
				atendenteVo.setDataAdmissao(resultSet.getDate("dataAdm"));
				atendenteVo.setVlpassagem(resultSet.getDouble("valorPassagem"));
				atendenteVo.setSalario(resultSet.getDouble("salario"));
				atendenteVo.setCargo(resultSet.getString("cargo"));
				atendenteVo.setIdAtendente(resultSet.getInt("idAtendente"));
				loginVo.setLogin(resultSet.getString("Acesso_login"));
				atendenteVo.setLoginVo(loginVo);
			}
			return atendenteVo;

		} catch (Exception sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
			closeConnection(connection);
		}

	}

	@Override
	public void excluirFuncionarioAtendente(Connection connection, Integer matricula) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(false);

			String query = "delete from funcionario where matricula=?";
			statement = connection.prepareStatement(query);

			statement.setInt(1, matricula);

			statement.executeUpdate();

		} catch (SQLException sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}

	}

	public void excluirAtendente(Connection connection, Integer matricula, String login) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(false);
			String query = "delete from atendente where Funcionario_matricula=? and Acesso_login=?";
			statement = connection.prepareStatement(query);

			statement.setInt(1, matricula);
			statement.setString(2, login);

			statement.executeUpdate();

		} catch (Exception sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}
	}

	@Override
	public void alterarAtendente(Connection connection, Integer matricula, String login) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(false);

			String query = "update atendente set Acesso_login=? where Funcionario_matricula=?";
			statement = connection.prepareStatement(query);

			statement.setInt(1, matricula);
			statement.setString(2, login);

			statement.executeUpdate();

		} catch (SQLException sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}


		
	}

	@Override
	public void alterarFuncionarioAtendente(Connection connection, AtendenteVo atendenteVo) throws IntegrationException {
		PreparedStatement statement = null;
		ResultSet resultSet = null;

		try {

			connection.setAutoCommit(Boolean.FALSE);

			String query = "update funcionario set nome=?,dataNasc=?,telefone=?,sexo=?,rg=?,orgEmissor=?,cpf=?,escolaridade=?,cursos=?,cartTrabalho=?,logradouro=?,numero=?,cep=?,bairro=?,subBairro=?,dataAdm=?,dataDem=?,valorPassagem=?,salario=?,cargo=? where matricula=?";
			statement = connection.prepareStatement(query);
			int i = 1;
			
			statement.setString(i, atendenteVo.getNome());
			statement.setDate(++i, new Date(atendenteVo.getDatanasc().getTime()));
			statement.setString(++i, atendenteVo.getTelefone());
			statement.setString(++i, atendenteVo.getSexo());
			statement.setLong(++i, atendenteVo.getRg());
			statement.setString(++i, atendenteVo.getOrgEmissor());
			statement.setString(++i, atendenteVo.getCpf());
			statement.setString(++i, atendenteVo.getEscolaridade());
			statement.setString(++i, atendenteVo.getCursos());
			statement.setInt(++i, atendenteVo.getNumCartTrabalho());
			statement.setString(++i, atendenteVo.getLogradouro());
			statement.setInt(++i, atendenteVo.getNumero());
			statement.setString(++i, atendenteVo.getCep());
			statement.setString(++i, atendenteVo.getBairro());
			statement.setString(++i, atendenteVo.getSubBairro());
			statement.setDate(++i, new Date(atendenteVo.getDataAdmissao().getTime()));
			statement.setDate(++i,new Date(atendenteVo.getDatanasc().getTime()));
			statement.setDouble(++i, atendenteVo.getVlpassagem().doubleValue());
			statement.setDouble(++i, atendenteVo.getSalario().doubleValue());
			statement.setString(++i, atendenteVo.getCargo());
			statement.setInt(++i, atendenteVo.getMatricula());
			
			statement.executeUpdate();

		} catch (SQLException sql) {
			throw new IntegrationException(sql.getMessage());
		} finally {
			closeResultSet(resultSet);
			closeStatement(statement);
		}


		
	}
	

}
